﻿using System;
using System.Collections.Generic;

using System.Text;
using System.Data;

namespace DBManager.Admin
{
    public class ProductsDAL
    {
        //download
        public static string AddDownloadProduct(string id, string LinkDownload, string ShortContent, string Status, string TypeProduct, string Title)
        {
            string kq = "0";
            string sql = @"INSERT INTO DownloadProduct 
           (id,LinkDownload,ShortContent,Status,TypeProduct ,Title) 
            VALUES
           (" + comm.to_sqlnumber(id) + @"
           ," + comm.to_sqltext(LinkDownload) + @"
           ," + comm.to_sqltext(ShortContent) + @"
           ," + comm.to_sqlnumber(Status) + @"
           ," + comm.to_sqlnumber(TypeProduct) + @"
           ," + comm.to_sqltext(Title) + ")";
            try
            {
                DBUtil.Execute(sql);
                kq = "1";
            }
            catch (Exception)
            {
                kq = "0";
            }
            return kq;
        }
        public static string EditDownloadProduct(string id, string LinkDownload, string ShortContent, string Status, string TypeProduct, string Title, string DownloadID)
        {
            string kq = "0";
            string sql = @"UPDATE DownloadProduct 
                          SET id = " + comm.to_sqlnumber(id) + @"
                          ,LinkDownload = " + comm.to_sqltext(LinkDownload) + @"
                          ,ShortContent = " + comm.to_sqltext(ShortContent) + @"
                          ,Status = " + comm.to_sqlnumber(Status) + @"
                          ,TypeProduct = " + comm.to_sqlnumber(TypeProduct) + @"
                          ,Title = " + comm.to_sqltext(Title) + @" 
                          WHERE DownloadID=" + comm.to_sqlnumber(DownloadID) + @"";
            try
            {
                DBUtil.Execute(sql);
                kq = "1";
            }
            catch (Exception)
            {
                kq = "0";
            }
            return kq;
        }
        public static DataTable GetAllListDownload(string id, string search, string checkStatus)
        {
            string caseType = @",case TypeProduct 
                                when 1 then N'Phần mềm' 
                                when 2 then N'Catalog (data sheet ) excel' 
                                when 3 then N'Hướng dẫn sử dụng' 
                                when 4 then N'Review sản phầm' 
                                end as KieuDownload ";
            string caseStatus = "case status when 0 then N'Không dùng' when 1 then N'Sử dụng' end as trangthai ";
            string dkSearch = " and title like N'%" + search + "%' ";
            string dkStatus = " and status=" + comm.to_sqlnumber(checkStatus) + " ";
            string sql = "select *," + caseStatus + caseType + " from DownloadProduct where id =" + comm.to_sqlnumber(id) + dkStatus + dkSearch + " order by downloadid desc";
            return DBUtil.SelectTable(sql);
        }
        public static DataTable GetInfomationDownloadPorduct(string DownloadID)
        {
            string sql = "select * from DownloadProduct where DownloadID =" + comm.to_sqlnumber(DownloadID);
            return DBUtil.SelectTable(sql);
        }
        public static string EditStatusDownload(string DownloadID, string status)
        {
            string kq = "0";
            string sql = @"update DownloadProduct set status="+comm.to_sqlnumber(status)+" where DownloadID="+comm.to_sqlnumber(DownloadID);
            try
            {
                DBUtil.Execute(sql);
                kq = "1";
            }
            catch (Exception)
            {
                kq = "0";
            }
            return kq;
        }
        //end download

        public static DataTable getAllZone()
        {
            string sql = "select * from ZoneProduct where ParentId = 0";
            return DBUtil.SelectTable(sql);
        }
        public static DataTable getAllZoneList()
        {
            string sql = "select * from ZoneProduct where status = 1";
            return DBUtil.SelectTable(sql);
        }
        public static DataTable getAllZoneByParentId(string ParentId)
        {
            string sql = "select * from ZoneProduct where ParentId =" + comm.to_sqlnumber(ParentId);
            return DBUtil.SelectTable(sql);
        }
        public static DataTable getInfomationZoneByID(string ZoneId)
        {
            string sql = "select * from ZoneProduct where ZoneId =" + comm.to_sqlnumber(ZoneId);
            return DBUtil.SelectTable(sql);
        }
        public static string addZone(string title, string url, string ParentId, string status)
        {
            string kq = "0";
            string sql = @"INSERT INTO ZoneProduct(title,url,ParentId,status)
                           VALUES 
                           (" + comm.to_sqltext(title) + @"
                           ," + comm.to_sqltext(url) + @"
                           ," + comm.to_sqlnumber(ParentId) + @"
                           ," + comm.to_sqlnumber(status) + @")";
            try
            {
                DBUtil.Execute(sql);
                kq = "1";
            }
            catch (Exception)
            {
                kq = "0";
            }
            return kq;
        }
        public static string editZone(string title, string url, string ParentId, string status, string zoneid)
        {
            string kq = "0";
            string sql = @"UPDATE ZoneProduct
                           SET title = " + comm.to_sqltext(title) + @"
                          ,url = " + comm.to_sqltext(url) + @"
                          ,ParentId = " + comm.to_sqlnumber(ParentId) + @"
                          ,status = " + comm.to_sqlnumber(status) + @"
                           WHERE zoneid=" + comm.to_sqlnumber(zoneid);
            try
            {
                DBUtil.Execute(sql);
                kq = "1";
            }
            catch (Exception)
            {
                kq = "0";
            }
            return kq;
        }
        public static string EditZoneCompany(string CompanyID, string zoneid)
        {
            string kq = "0";
            string sql = @"UPDATE ZoneProduct
                           SET CompanyID = CompanyID + " + comm.to_sqltext(CompanyID) + @"
                           WHERE zoneid=" + comm.to_sqlnumber(zoneid);
            try
            {
                string sqlCheck = "select * from ZoneProduct where zoneid=" + comm.to_sqlnumber(zoneid);
                DataTable dtCheck = DBUtil.SelectTable(sqlCheck);
                if (dtCheck.Rows.Count > 0)
                {
                    if (dtCheck.Rows[0]["CompanyID"].ToString().Contains(CompanyID))
                    { //da co k vao
                        kq = "0";
                    }
                    else
                    {
                        if (dtCheck.Rows[0]["CompanyID"].ToString() == "")
                        {
                            sql = @"UPDATE ZoneProduct
                           SET CompanyID = " + comm.to_sqltext(CompanyID) + @"
                           WHERE zoneid=" + comm.to_sqlnumber(zoneid);
                        }
                        DBUtil.Execute(sql);
                        kq = "1";
                    }
                }
            }
            catch (Exception)
            {
                kq = "0";
            }
            return kq;
        }
        //products

        public static string EditStatusProduct(string ProductID, string status)
        {
            string kq = "0";
            string sql = @"update Products set status=" + comm.to_sqlnumber(status) + " where ID=" + comm.to_sqlnumber(ProductID);
            try
            {
                DBUtil.Execute(sql);
                kq = "1";
            }
            catch (Exception)
            {
                kq = "0";
            }
            return kq;
        }
        public static void UpdateBuildLink()
        {
            string sql = "select top 1 ID from Products order by id desc";
            DataTable dtInfo = DBUtil.SelectTable(sql);
            string sqlUpdate = "update Products set url=url+'p" + dtInfo.Rows[0]["id"].ToString() + ".htm' where id=" + comm.to_sqlnumber(dtInfo.Rows[0]["id"].ToString());
            DBUtil.Execute(sqlUpdate);
        }
        public static DataTable getAllProductsByNewsRelation(string NewsRelation)
        {
            DataTable dt = new DataTable();
            if (NewsRelation != "")
            {
                string sql = "select id,title from Products where id in (" + NewsRelation + ") ";
                dt = DBUtil.SelectTable(sql);
            }
            return dt;
        }
        public static DataTable getAllProducts(string sFind, string sZoneID, string typeFind, string checkStatus)
        {
            string dk = "";
            string dkZone = "";
            if (sFind != "")
            {
                dk = " and pr.title like '%" + sFind + "%' or pr.sapo like '%" + sFind + "%' ";
            }
            if (sZoneID != "0")
            {
                dkZone = " and (pr.zoneid=" + comm.to_sqlnumber(sZoneID) + " or pr.ZoneIdOther like '%" + sZoneID + "," + "%' )";
            }
            string orderBy = "";
            string dkFocusHome = "";
            if (typeFind == "1")
            {
                orderBy = " order by pr.id desc";
            }
            else if (typeFind == "2")
            {
                orderBy = " order by pr.CountVisit desc";
            }
            else if (typeFind == "3")
            {
                orderBy = " order by pr.id desc";
                dkFocusHome = " and isfocus=1 ";
            }
            string dkStatus = "";
            dkStatus = " and pr.status=" + comm.to_sqlnumber(checkStatus) + " ";
            string caseTypeSale = " ,case pr.TypeSale when 0 then N'Hiện giá' when 1 then N'Liên hệ' end as kieuban ";
            string caseCheckQuantity = " ,case pr.CheckQuantity when 0 then N'Hết hàng' when 1 then N'Còn hàng' end as checkhang ";
            string sql = @"select pr.url,pr.ProductCode,pr.FilterConditions,pr.Warranty,pr.EndDateNew,pr.CountVisit,co.namecp,pr.CompanyID,pr.TypeSale,pr.price,pr.sale,pr.LastModifiedDate,pr.DistributionDate,pr.id,case pr.status when 0 then N'Không Dùng' when 1 then N'Dùng' end as trangthai,pr.title,pr.Avatar" + caseTypeSale + caseCheckQuantity + @" from 
            Products pr inner join Companies co on pr.CompanyID=co.CompanyID where 0=0 " + dkFocusHome + dkStatus + dk + dkZone + orderBy;
            return DBUtil.SelectTable(sql);
        }
        public static DataTable GetInfomationProduct(string newsID)
        {
            string sql = "select * from Products where id=" + comm.to_sqlnumber(newsID);
            return DBUtil.SelectTable(sql);
        }
        public static string addProduct(string ZoneId, string ZoneIdOther, string Title, string Sapo, string Avatar, string Author, string IsFocus, string Url, string Tags, string status, string longContent, string price, string sale, string CompanyID, string TypeSale, string CheckQuantity, string EndDateNew, string Warranty, string FilterConditions, string Specifications, string NewsRelation, string ProductCode)
        {
            //them moi san pham
            string kq = "0";
            try
            {
                string sql = @"INSERT INTO Products
                       (
                       ZoneId
                       ,ZoneIdOther
                       ,Title
                       ,Sapo
                       ,Avatar
                       ,Author
                       ,IsFocus
                       ,DistributionDate
                       ,Url
                       ,Tags
                       ,status
                       ,LastModifiedDate
                       ,price
                       ,sale
                       ,CompanyID
                       ,TypeSale
                       ,CheckQuantity
                       ,EndDateNew
                       ,Warranty
                       ,FilterConditions
                       ,Specifications
                       ,NewsRelation
                       ,ProductCode
                       ,longContent)
                        VALUES
                       (
                       " + comm.to_sqlnumber(ZoneId) + @"
                       ," + comm.to_sqltext(ZoneIdOther) + @"
                       ," + comm.to_sqltext(Title) + @"
                       ," + comm.to_sqltext(Sapo) + @"
                       ," + comm.to_sqltext(Avatar) + @"
                       ," + comm.to_sqltext(Author) + @"
                       ," + comm.to_sqlnumber(IsFocus) + @"
                       ,getdate()
                       ," + comm.to_sqltext(Url) + @"
                       ," + comm.to_sqltext(Tags) + @"
                       ," + comm.to_sqlnumber(status) + @"
                       ,getdate()
                       ," + comm.to_sqlnumber(price) + @"
                       ," + comm.to_sqlnumber(sale) + @"
                       ," + comm.to_sqlnumber(CompanyID) + @"
                       ," + comm.to_sqlnumber(TypeSale) + @"
                       ," + comm.to_sqlnumber(CheckQuantity) + @"
                       ," + comm.to_sqltext(EndDateNew) + @"
                       ," + comm.to_sqltext(Warranty) + @"
                       ," + comm.to_sqltext(FilterConditions) + @"
                       ," + comm.to_sqltext(Specifications) + @"
                       ," + comm.to_sqltext(NewsRelation) + @"
                       ," + comm.to_sqltext(ProductCode) + @"
                       ," + comm.to_sqltext(longContent) + ")";
                DBUtil.Execute(sql);
                kq = "1";
            }
            catch (Exception)
            {
                kq = "0";
            }
            return kq;
        }
        public static string EditProduct(string ID, string ZoneId, string ZoneIdOther, string Title, string Sapo, string Avatar, string Author, string IsFocus, string Url, string Tags, string status, string longContent, string price, string sale, string CompanyID, string TypeSale, string CheckQuantity, string EndDateNew, string Warranty, string FilterConditions, string Specifications, string NewsRelation, string ProductCode)
        {
            //sua san pham
            string kq = "0";
            try
            {
                string sql = @"UPDATE Products
                           SET
                           ZoneId = " + comm.to_sqlnumber(ZoneId) + @"
                          ,ZoneIdOther = " + comm.to_sqltext(ZoneIdOther) + @"
                          ,Title = " + comm.to_sqltext(Title) + @"
                          ,Sapo = " + comm.to_sqltext(Sapo) + @"
                          ,Avatar = " + comm.to_sqltext(Avatar) + @"
                          ,Author = " + comm.to_sqltext(Author) + @"
                          ,IsFocus = " + comm.to_sqlnumber(IsFocus) + @"
                          ,Url = " + comm.to_sqltext(Url) + @"
                          ,Tags = " + comm.to_sqltext(Tags) + @"
                          ,status = " + comm.to_sqlnumber(status) + @"
                          ,price = " + comm.to_sqlnumber(price) + @"
                          ,sale = " + comm.to_sqlnumber(sale) + @"
                          ,CompanyID = " + comm.to_sqlnumber(CompanyID) + @"
                          ,TypeSale = " + comm.to_sqlnumber(TypeSale) + @"
                          ,LastModifiedDate = getdate()
                          ,longContent = " + comm.to_sqltext(longContent) + @"
                          ,CheckQuantity = " + comm.to_sqlnumber(CheckQuantity) + @"
                          ,Warranty = " + comm.to_sqltext(Warranty) + @"
                          ,FilterConditions = " + comm.to_sqltext(FilterConditions) + @"
                          ,Specifications = " + comm.to_sqltext(Specifications) + @"
                          ,EndDateNew = " + comm.to_sqltext(EndDateNew) + @"
                          ,NewsRelation = " + comm.to_sqltext(NewsRelation) + @"
                          ,ProductCode = " + comm.to_sqltext(ProductCode) + @"
                          WHERE id=" + comm.to_sqlnumber(ID);
                DBUtil.Execute(sql);
                kq = "1";
            }
            catch (Exception)
            {
                kq = "0";
            }
            return kq;
        }

    }
}
